﻿
USE [Sector7]

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

/************************************************************************************************************************** DROP CONSTRAINT */

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_DataElement_S7_DataGroup]') AND
	parent_object_id = OBJECT_ID(N'[dbo].[S7_DataElement]')
) ALTER TABLE [dbo].[S7_DataElement]
	DROP CONSTRAINT [FK_S7_DataElement_S7_DataGroup]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_DataTemplate_S7_DataGroup]') AND 
	parent_object_id = OBJECT_ID(N'[dbo].[S7_DataTemplate]')
) ALTER TABLE [dbo].[S7_DataTemplate]
	DROP CONSTRAINT [FK_S7_DataTemplate_S7_DataGroup]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_ValueDomain_S7_DataGroup]') AND 
	parent_object_id = OBJECT_ID(N'[dbo].[S7_ValueDomain]')
) ALTER TABLE [dbo].[S7_ValueDomain] 
	DROP CONSTRAINT [FK_S7_ValueDomain_S7_DataGroup]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_ValueDomainItem_S7_ValueDomain]') AND
	parent_object_id = OBJECT_ID(N'[dbo].[S7_ValueDomainItem]')
) ALTER TABLE [dbo].[S7_ValueDomainItem]
	DROP CONSTRAINT [FK_S7_ValueDomainItem_S7_ValueDomain]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_DataElement_S7_ValueDomain]') AND
	parent_object_id = OBJECT_ID(N'[dbo].[S7_DataElement]')
) ALTER TABLE [dbo].[S7_DataElement]
	DROP CONSTRAINT [FK_S7_DataElement_S7_ValueDomain]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_DataElement_S7_ValueType]') AND
	parent_object_id = OBJECT_ID(N'[dbo].[S7_DataElement]')
) ALTER TABLE [dbo].[S7_DataElement]
	DROP CONSTRAINT [FK_S7_DataElement_S7_ValueType]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_DataTemplateItem_S7_Metadata]') AND 
	parent_object_id = OBJECT_ID(N'[dbo].[S7_DataTemplateItem]')
) ALTER TABLE [dbo].[S7_DataTemplateItem]
	DROP CONSTRAINT [FK_S7_DataTemplateItem_S7_Metadata]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_DataTemplateItem_S7_DataTemplate]') AND 
	parent_object_id = OBJECT_ID(N'[dbo].[S7_DataTemplateItem]')
) ALTER TABLE [dbo].[S7_DataTemplateItem]
	DROP CONSTRAINT [FK_S7_DataTemplateItem_S7_DataTemplate]

IF EXISTS (
	SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_S7_Metadata_S7_DataElement]') AND
	parent_object_id = OBJECT_ID(N'[dbo].[S7_Metadata]')
) ALTER TABLE [dbo].[S7_Metadata]
	DROP CONSTRAINT [FK_S7_Metadata_S7_DataElement]

/************************************************************************************************************************** [dbo].[S7_DataGroup] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_DataGroup]') AND
	type in (N'U')
) DROP TABLE [dbo].[S7_DataGroup]

CREATE TABLE [dbo].[S7_DataGroup](
	[GroupId] [int] IDENTITY(1,1) NOT NULL,
	[GroupIdentifier] [nvarchar](64) NOT NULL,
	[GroupName] [nvarchar](128) NOT NULL,
	[GroupType] [int] NOT NULL,
	[MajorVersion] [nvarchar](10) NULL,
	[MinorVersion] [nvarchar](10) NULL,
	[RevisionNumber] [nvarchar](10) NULL,
	CONSTRAINT [PK_S7_DataGroup]
	PRIMARY KEY CLUSTERED (
		[GroupId] ASC
	) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

/************************************************************************************************************************** [dbo].[S7_ValueType] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_ValueType]') AND
	type in (N'U')
) DROP TABLE [dbo].[S7_ValueType]

CREATE TABLE [dbo].[S7_ValueType](
	[TypeId] [int] IDENTITY(1,1) NOT NULL,
	[TypeName] [nvarchar](64) NOT NULL,
	[TypeIdentifier] [nvarchar](16) NOT NULL,
	[TypeDescription] [nvarchar](128) NOT NULL,
	CONSTRAINT [PK_S7_ValueType]
	PRIMARY KEY CLUSTERED (
		[TypeId] ASC
	) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

/************************************************************************************************************************** [dbo].[S7_ValueDomain] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_ValueDomain]') AND
	type in (N'U')
) DROP TABLE [dbo].[S7_ValueDomain]

CREATE TABLE [dbo].[S7_ValueDomain](
	[DomainId] [nvarchar](32) NOT NULL,
	[DomainName] [nvarchar](64) NOT NULL,
	[GroupId] [int] NOT NULL,
	CONSTRAINT [PK_S7_ValueDomain]
	PRIMARY KEY CLUSTERED (
		[DomainId] ASC
	) WITH (
		PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		IGNORE_DUP_KEY = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[S7_ValueDomain]  
	WITH CHECK ADD CONSTRAINT [FK_S7_ValueDomain_S7_DataGroup] 
	FOREIGN KEY([GroupId])
	REFERENCES [dbo].[S7_DataGroup] ([GroupId])
ALTER TABLE [dbo].[S7_ValueDomain] 
	CHECK CONSTRAINT [FK_S7_ValueDomain_S7_DataGroup]

/************************************************************************************************************************** [dbo].[S7_ValueDomainItem] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_ValueDomainItem]') AND
	type in (N'U')
) DROP TABLE [dbo].[S7_ValueDomainItem]

CREATE TABLE [dbo].[S7_ValueDomainItem](
	[DomainItemId] [int] IDENTITY(1,1) NOT NULL,
	[DomainItemValue] [nvarchar](16) NOT NULL,
	[DomainItemImplication] [nvarchar](128) NOT NULL,
	[DomainItemDescription] [nvarchar](512) NULL,
	[DomainId] [nvarchar](32) NOT NULL,
	CONSTRAINT [PK_S7_ValueDomainItem]
	PRIMARY KEY CLUSTERED (
		[DomainItemId] ASC
	) WITH (
		PAD_INDEX = OFF, 
		STATISTICS_NORECOMPUTE = OFF, 
		IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS = ON, 
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[S7_ValueDomainItem]
	WITH CHECK ADD CONSTRAINT [FK_S7_ValueDomainItem_S7_ValueDomain]
	FOREIGN KEY([DomainId])
	REFERENCES [dbo].[S7_ValueDomain] ([DomainId])
ALTER TABLE [dbo].[S7_ValueDomainItem]
	CHECK CONSTRAINT [FK_S7_ValueDomainItem_S7_ValueDomain]

/************************************************************************************************************************** [dbo].[S7_DataElement] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_DataElement]') AND 
	type in (N'U')
) DROP TABLE [dbo].[S7_DataElement]

CREATE TABLE [dbo].[S7_DataElement](
	[ElementId] [nvarchar](32) NOT NULL,
	[ElementName] [nvarchar](64) NOT NULL,
	[ElementDescription] [nvarchar](1024) NOT NULL,
	[ElementFormat] [nvarchar](16) NOT NULL,
	[TypeId] [int] NOT NULL,
	[DomainId] [nvarchar](32) NULL,
	[GroupId] [int] NOT NULL,
	CONSTRAINT [PK_S7_DataElement]
	PRIMARY KEY CLUSTERED (
		[ElementId] ASC
	) WITH (
		PAD_INDEX = OFF, 
		STATISTICS_NORECOMPUTE = OFF, 
		IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS = ON, 
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[S7_DataElement]  
	WITH CHECK ADD CONSTRAINT [FK_S7_DataElement_S7_DataGroup] 
	FOREIGN KEY([GroupId])
	REFERENCES [dbo].[S7_DataGroup] ([GroupId])
ALTER TABLE [dbo].[S7_DataElement]
	CHECK CONSTRAINT [FK_S7_DataElement_S7_DataGroup]

ALTER TABLE [dbo].[S7_DataElement]
	WITH CHECK ADD CONSTRAINT [FK_S7_DataElement_S7_ValueDomain]
	FOREIGN KEY([DomainId])
	REFERENCES [dbo].[S7_ValueDomain] ([DomainId])
ALTER TABLE [dbo].[S7_DataElement]
	CHECK CONSTRAINT [FK_S7_DataElement_S7_ValueDomain]

ALTER TABLE [dbo].[S7_DataElement]
	WITH CHECK ADD CONSTRAINT [FK_S7_DataElement_S7_ValueType] 
	FOREIGN KEY([TypeId])
	REFERENCES [dbo].[S7_ValueType] ([TypeId])
ALTER TABLE [dbo].[S7_DataElement] 
	CHECK CONSTRAINT [FK_S7_DataElement_S7_ValueType]

/************************************************************************************************************************** [dbo].[S7_Metadata] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_Metadata]') AND 
	type in (N'U')
) DROP TABLE [dbo].[S7_Metadata]

CREATE TABLE [dbo].[S7_Metadata](
	[MetadataId] [nvarchar](32) NOT NULL,
	[ElementId] [nvarchar](32) NOT NULL,
	[ColumnName] [nvarchar](64) NOT NULL,
	CONSTRAINT [PK_S7_Metadata] 
	PRIMARY KEY CLUSTERED (
		[MetadataId] ASC
	) WITH (
		PAD_INDEX = OFF, 
		STATISTICS_NORECOMPUTE = OFF, 
		IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS = ON, 
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[S7_Metadata]  
	WITH CHECK ADD CONSTRAINT [FK_S7_Metadata_S7_DataElement] 
	FOREIGN KEY([ElementId])
	REFERENCES [dbo].[S7_DataElement] ([ElementId])
ALTER TABLE [dbo].[S7_Metadata] 
	CHECK CONSTRAINT [FK_S7_Metadata_S7_DataElement]

/************************************************************************************************************************** [dbo].[S7_DataTemplate] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_DataTemplate]') AND 
	type in (N'U')
) DROP TABLE [dbo].[S7_DataTemplate]

CREATE TABLE [dbo].[S7_DataTemplate](
	[TemplateId] [int] IDENTITY(1,1) NOT NULL,
	[TemplateName] [nvarchar](64) NOT NULL,
	[TableName] [nvarchar](16) NOT NULL,
	[GroupId] [int] NOT NULL,
	CONSTRAINT [PK_S7_DataTemplate]
	PRIMARY KEY CLUSTERED (
		[TemplateId] ASC
	) WITH (
		PAD_INDEX = OFF, 
		STATISTICS_NORECOMPUTE = OFF, 
		IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS = ON, 
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[S7_DataTemplate]  
	WITH CHECK ADD CONSTRAINT [FK_S7_DataTemplate_S7_DataGroup] 
	FOREIGN KEY([GroupId])
	REFERENCES [dbo].[S7_DataGroup] ([GroupId])
ALTER TABLE [dbo].[S7_DataTemplate] 
	CHECK CONSTRAINT [FK_S7_DataTemplate_S7_DataGroup]

/************************************************************************************************************************** [dbo].[S7_DataTemplateItem] */
IF EXISTS (
	SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[S7_DataTemplateItem]') AND 
	type in (N'U')
) DROP TABLE [dbo].[S7_DataTemplateItem]

CREATE TABLE [dbo].[S7_DataTemplateItem](
	[TemplateItemId] [int] IDENTITY(1,1) NOT NULL,
	[MetadataId] [nvarchar](32) NOT NULL,
	[TemplateId] [int] NOT NULL,
	CONSTRAINT [PK_S7_DataTemplateItem]
	PRIMARY KEY CLUSTERED (
		[TemplateItemId] ASC
	) WITH (
		PAD_INDEX = OFF, 
		STATISTICS_NORECOMPUTE = OFF, 
		IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS = ON, 
		ALLOW_PAGE_LOCKS = ON
	) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[S7_DataTemplateItem]  
	WITH CHECK ADD CONSTRAINT [FK_S7_DataTemplateItem_S7_DataTemplate] 
	FOREIGN KEY([TemplateId])
	REFERENCES [dbo].[S7_DataTemplate] ([TemplateId])
ALTER TABLE [dbo].[S7_DataTemplateItem] 
	CHECK CONSTRAINT [FK_S7_DataTemplateItem_S7_DataTemplate]

ALTER TABLE [dbo].[S7_DataTemplateItem]  
	WITH CHECK ADD CONSTRAINT [FK_S7_DataTemplateItem_S7_Metadata] 
	FOREIGN KEY([MetadataId])
	REFERENCES [dbo].[S7_Metadata] ([MetadataId])
ALTER TABLE [dbo].[S7_DataTemplateItem] 
	CHECK CONSTRAINT [FK_S7_DataTemplateItem_S7_Metadata]